package com.dao;

import com.util.Constant;
import com.util.Printer;

import java.sql.*;

/**
 * Created by Phillip on 2014/4/26.
 */
public class VacancyDaoImpl implements VacancyDao {

    Connection connection = null;

    public VacancyDaoImpl() {
        connect();
    }

    void connect() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(Constant.CONNECTION_STRING, Constant.USERNAME_STRING, "");
        } catch (Exception e) {
            System.out.println("Fail to build connection!");
            return;
        }
    }

    @Override
    public String bookByFlightDateClass(String flightString1, String dateString1, String classString1, String flightString2, String dateString2,
                                        String classString2, int tableInt) {
        if (connection == null) {
            Printer.p("Please establish connection first!");
            return Constant.NOCONNECTION_STRING;
        } else {

            int rows = 0;
            String sqlString = "update flight" + tableInt + " set VACA = VACA - 1 where "
                    + "(FLNO = ? and CLAS = ? and TO_DAYS(DPDT) = TO_DAYS(?) and VACA > 0 ) " + "or"
                    + " ( FLNO = ? and CLAS = ? and TO_DAYS(DPDT) = TO_DAYS(?) and VACA > 0)";
            String sqlCheckString = "select VACA from flight" + tableInt + " where FLNO = ? and CLAS = ? and TO_DAYS(DPDT) = TO_DAYS(?)";
            try {

                PreparedStatement preparedStatement = connection.prepareStatement(sqlCheckString);
                preparedStatement.setString(1, flightString1);
                preparedStatement.setString(2, classString1);
                preparedStatement.setString(3, dateString1);
                ResultSet resultSet = preparedStatement.executeQuery();
                if (resultSet.next()) {
                    if (resultSet.getInt(1) == 0) {
                        return Constant.NODEPTSEAT_STRING;
                    }
                }
                preparedStatement.setString(1, flightString2);
                preparedStatement.setString(2, classString2);
                preparedStatement.setString(3, dateString2);
                resultSet = preparedStatement.executeQuery();
                if (resultSet.next()) {
                    if (resultSet.getInt(1) == 0) {
                        return Constant.NORETURNSEAT_STRING;
                    }
                }
                preparedStatement = connection.prepareStatement(sqlString);
                preparedStatement.setString(1, flightString1);
                preparedStatement.setString(2, classString1);
                preparedStatement.setString(3, dateString1);
                preparedStatement.setString(4, flightString2);
                preparedStatement.setString(5, classString2);
                preparedStatement.setString(6, dateString2);

                rows = preparedStatement.executeUpdate();

                if (rows == 2) {
                    return Constant.BOOKSUCCESSFUL_STRING;
                } else {
                    return Constant.BOOKFAILED_STRING;
                }
            } catch (SQLException e) {
                e.printStackTrace();
                return "Error!";
            }
        }
    }



    public void disconnect() {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return;
        }
    }


}
